﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Security.Principal;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace SPaDevToolkit.Administration
{
    public abstract class SPaWebServiceApplicationDatabase : SPDatabase, ISPaWebServiceApplicationDatabase
    {
        #region Constructors
        public SPaWebServiceApplicationDatabase() { }

        public SPaWebServiceApplicationDatabase(SPDatabaseParameters databaseParameters)
            : base(databaseParameters) { }

        public SPaWebServiceApplicationDatabase(string name, SPDatabaseServiceInstance serviceInstance)
            : base(name, serviceInstance) { }
            
        #endregion

        #region Overrides
        public new bool WasCreated
        {
            get { return base.WasCreated; }

        }

        #endregion

        #region ISPaWebServiceApplicationDatabase Members

        public void AddRoleMember(string role, string user)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role");
            }

            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            SqlCommand databaseUserCommand = GetDatabaseUserCommand(user);
            databaseUserCommand.Connection = new SqlConnection(this.DatabaseConnectionString);
            databaseUserCommand.CommandText = databaseUserCommand.CommandText + "if not user_id(@db_user_name) in (1,0,3,4) exec @add_result = sp_addrolemember @role, @db_user_name";
            databaseUserCommand.Parameters.Add("@role", SqlDbType.NVarChar, 0x80).Value = role;
            SqlParameter parameter = databaseUserCommand.Parameters.Add("@add_result", SqlDbType.Int);
            parameter.Direction = ParameterDirection.Output;
            databaseUserCommand.ExecuteNonQuery();

            if (DBNull.Value != parameter.Value && (int)parameter.Value != 0)
            {
                throw new SPDatabaseException(string.Format(CultureInfo.InvariantCulture, "AddRoleMember(). Exception occured whilst adding role to user '{0}'", user));
            }
        }


        /// <summary>
        /// Grants the access.
        /// </summary>
        /// <param name="user">The user.</param>
        public void GrantAccess(string user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = new SqlConnection(this.DatabaseConnectionString);
                command.CommandType = CommandType.Text;
                command.CommandText = "declare @db_user_name sysname\r\nselect @db_user_name = name from dbo.sysusers where sid = SUSER_SID(@user_name) and hasdbaccess = 1\r\nif (@db_user_name is null)\r\nbegin\r\nif exists(select * from dbo.sysusers where name = @user_name)\r\nexec @revoke_result = sp_revokedbaccess @user_name\r\nexec @grant_result = sp_grantdbaccess @user_name, @db_user_name output\r\nend";
                command.Parameters.Add("@user_name", SqlDbType.NVarChar, 0x80).Value = user;
                SqlParameter revokeResultParam = command.Parameters.Add("@revoke_result", SqlDbType.Int);
                revokeResultParam.Direction = ParameterDirection.Output;
                SqlParameter grantResultParam = command.Parameters.Add("@grant_result", SqlDbType.Int);
                grantResultParam.Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();

                if (DBNull.Value != grantResultParam.Value && (int)grantResultParam.Value != 0)
                {
                    throw new SPDatabaseException(string.Format(CultureInfo.InvariantCulture, "GrantAccess(). Cannot add user '{0}' to the database", user));
                }
            }
        }

        /// <summary>
        /// Determines whether the specified user has access.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns>
        ///   <c>true</c> if the specified user has access; otherwise, <c>false</c>.
        /// </returns>
        public bool HasAccess(string user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = new SqlConnection(this.DatabaseConnectionString);
                command.CommandType = CommandType.Text;
                command.CommandText = "select @has_access = case when exists (select * from dbo.sysusers where (sid = SUSER_SID(@user_name)) and (hasdbaccess = 1)) then 1 else 0 end";
                SqlParameter parameter = command.Parameters.Add("@has_access", SqlDbType.Int);
                parameter.Direction = ParameterDirection.Output;
                command.Parameters.Add("@user_name", SqlDbType.NVarChar, 0x80).Value = user;
                command.ExecuteNonQuery();
                return 1 == (int)parameter.Value;
            }
        }

        public void EnsureAccess(string userName)
        {
            if (this.HasAccess(userName))
            {
                this.GrantAccess(userName);
            }

            AddRoleMember("db_owner", userName);
        }

        /// <summary>
        /// Grants the application pool access.
        /// </summary>
        /// <param name="processSecurityIdentifier">The process security identifier.</param>
        public void GrantDBOwnerAccess(SecurityIdentifier processSecurityIdentifier)
        {
            this.GrantAccess(processSecurityIdentifier, "db_owner");
        }

        /// <summary>
        /// Sets the authentication mode.
        /// </summary>
        /// <param name="sqlAuthUserName">Name of the SQL auth user.</param>
        /// <param name="sqlAuthPassword">The SQL auth password.</param>
        public void SetSqlAuthenticationCredentials(string sqlAuthUserName, string sqlAuthPassword)
        {
            if (string.IsNullOrEmpty(sqlAuthUserName))
            {
                sqlAuthUserName = null;
            }

            if (string.IsNullOrEmpty(sqlAuthPassword))
            {
                sqlAuthPassword = null;
            }

            this.Username = sqlAuthUserName;
            this.Password = sqlAuthPassword;
        }

        public void UpdateDatabaseAuth(SPDatabaseParameters parameters)
        {
            this.Username = parameters.Username;
            this.Password = parameters.Password;
            if (!string.IsNullOrEmpty(parameters.FailoverPartner) && ((this.FailoverServiceInstance == null) || !this.FailoverServiceInstance.Name.Equals(parameters.FailoverPartner)))
            {
                this.AddFailoverServiceInstance(parameters.FailoverPartner);
            }
            this.Update();
        }
        #endregion

        #region Other
        private System.Data.SqlClient.SqlCommand GetDatabaseUserCommand(string user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(this.DatabaseConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = "declare @db_user_name sysname select @db_user_name = name from dbo.sysusers where sid = SUSER_SID(@user_name) ";
            command.Parameters.Add("@user_name", SqlDbType.NVarChar, 0x80).Value = user;
            return command;
        }

        #endregion
    }
}
